#coding=utf-8

from sqlserver import *
import pymysql
import datetime
#

consistence_file='/home/marvin/daily_job/py_job/consistence'
# consistence_file='consistence'

now=datetime.datetime.now()
today=now.strftime("%Y-%m-%d")
before=(now+datetime.timedelta(days=-1,hours=-0.5)).strftime("%Y-%m-%d")  ##c为str（即字符串），将时间格式化成字符串
print before,today
host = '192.168.0.34'
port = 3306
dbname = 'result'
user = 'root'
passwd = 'root'
sql1 = '''
SELECT SUM(oilLitre)
from res_oil where
minutes>'%s' and minutes<'%s'
and oils in ('92#','95#','97#','0#','80#','98#','-10#','-20#','-35#')
     '''
sql2 = '''
SELECT SUM(litre0+litre80+litre92+litre95+litre97+litre98+litre_10+litre_20+litre_35) from res_oilb where
minutes>'%s' and minutes<'%s'
       '''
sql3 = '''
SELECT stationid,SUM(oilLitre)
from res_oil where
minutes>='%s' and minutes<'%s'
group by stationid
     '''
sql4 = '''
select
SUM(Y.item_quantity)
      from transactions as Z
      join transaction_items as Y
      on Z.transaction_id=Y.transaction_id
      join transaction_items_extra as X on Z.transaction_id=X.transaction_id
      and X.item_number=Y.item_number
      where Y.item_type!=20 and X.product_code like '%s'
 and Z.transaction_date>='%s' and Z.transaction_date<'%s'
     '''
sql1 = sql1 % (before,today)
sql2 = sql2 % (before,today)
sql3 = sql3 % (before,today)
sql4 = sql4 % ('%#%',before,today)

# print sql4

def doCheck():
    db = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=dbname, charset='utf8')
    cur = db.cursor()
    print sql1
    print sql2
    cur.execute(sql1)
    res_oil= cur.fetchall()[0][0]
    cur.execute(sql2)
    res_oilb = cur.fetchall()[0][0]
    cur.close()
    res_oil=float(res_oil)
    res_oilb=float(res_oilb)
    if res_oil-res_oilb>-5 and res_oil-res_oilb<5:
        print 'res_oil:',res_oil,'res_oilb:',res_oilb
    else:
        print 'res_oil:', res_oil, 'res_oilb:', res_oilb
        with open(consistence_file,'w') as f:
            f.write('1')

def doCheck2():
    db = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=dbname, charset='utf8')
    cur = db.cursor()
    cur.execute(sql3)
    middb_data = cur.fetchall()
    middb_data = dict(middb_data)
    cur.close()
    flag = False
    for k in sqlserver.keys():
        try:
            count1 = middb_data.get(k,0.0)
            ms = ODBC_MS('{SQL SERVER}', sqlserver.get(k)[1], 'enablerdb', sqlserver.get(k)[2],
                         sqlserver.get(k)[3])
            count2 = ms.ExecQuery(sql4)[0][0]
            if count2 - count1>-10.0 and count2-count1<10.0:
                print 'check station=%s,no problem!!!  detail:sqlserv:%d,middb:%d' % (k, count2, count1)
            else:
                print 'warning station=%s!!!!!!!!!!!  detail:sqlserv:%d,middb:%d' % (k, count2, count1)
                flag = True
        except:
            print 'Exception!!!!!!!  station=' + str(k)

    # 发现数据不一致，发送邮件
    print flag
    if flag:
        with open(consistence_file, 'w') as f:
            f.write('1')
    else:
        with open(consistence_file, 'w') as f:
            f.write('0')

if __name__ == '__main__':
    # pass
    doCheck()
    doCheck2()
